package com.columbia;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellReference;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFFormulaEvaluator;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;

/**
 * User: csavard
 * Date: 13-06-16
 */
public final class ExcelUtil {
    /**
     * Contructeur prive pour ne pas etre instatié
     */
    private ExcelUtil() {
    }


    public static String getStringValue(XSSFCell cell) {
        String value = null;
        if (cell != null) {
            switch (cell.getCellType()) {
                case XSSFCell.CELL_TYPE_STRING:
                    value = cell.getStringCellValue();
                    break;
                case XSSFCell.CELL_TYPE_NUMERIC:
                    value = String.valueOf((long) cell.getNumericCellValue());
                    break;
                case XSSFCell.CELL_TYPE_BLANK:
                    break;
                case XSSFCell.CELL_TYPE_BOOLEAN:
                    value = String.valueOf(cell.getBooleanCellValue());
                    break;
                case XSSFCell.CELL_TYPE_FORMULA:
                    XSSFFormulaEvaluator formulaEvaluator = cell.getRow().getSheet().getWorkbook().getCreationHelper().createFormulaEvaluator();
                    CellValue evaluate = formulaEvaluator.evaluate(cell);
                    value = evaluate.formatAsString();
                    if (value.startsWith("\"")) {
                        value = value.substring(1, value.length() - 1);
                    }
                    break;
            }
        }

        return value;
    }

    public static String getStringValue(HSSFCell cell) {
        String value = null;
        if (cell != null) {
            switch (cell.getCellType()) {
                case HSSFCell.CELL_TYPE_STRING:
                    value = cell.getStringCellValue();
                    break;
                case HSSFCell.CELL_TYPE_NUMERIC:
                    value = String.valueOf((long) cell.getNumericCellValue());
                    break;
                case HSSFCell.CELL_TYPE_BLANK:
                    break;
                case HSSFCell.CELL_TYPE_BOOLEAN:
                    value = String.valueOf(cell.getBooleanCellValue());
                    break;
                case HSSFCell.CELL_TYPE_FORMULA:
                    HSSFFormulaEvaluator formulaEvaluator = cell.getRow().getSheet().getWorkbook().getCreationHelper().createFormulaEvaluator();
                    CellValue evaluate = formulaEvaluator.evaluate(cell);
                    value = evaluate.formatAsString();
                    if (value.startsWith("\"")) {
                        value = value.substring(1, value.length() - 1);
                    }
                    break;
            }
        }

        return value;
    }

    public static String convertIndexToColString(int index) {
        return CellReference.convertNumToColString(index);
    }

    public static int convertColStringToIndex(String ref) {
        return CellReference.convertColStringToIndex(ref);
    }
}
